<?php
namespace app\common;

class Excel {

	/** 
	 * 创建(导出)Excel数据表格 
	 * @param  array   $list        要导出的数组格式的数据 
	 * @param  string  $filename    导出的Excel表格数据表的文件名 
	 * @param  array   $indexKey    $list数组中与Excel表格表头$header中每个项目对应的字段的名字(key值) 
	 * @param  array   $startRow    第一条数据在Excel表格中起始行 
	 * @param  [bool]  $excel2007   是否生成Excel2007(.xlsx)以上兼容的数据表 
	 * 比如: $indexKey与$list数组对应关系如下: 
	 *     $indexKey = array('id','username','sex','age'); 
	 *     $list = array(array('id'=>1,'username'=>'YQJ','sex'=>'男','age'=>24)); 
	 */  
	public static function toExcel($list,$filename,$indexKey,$startRow=1,$excel2007=true) {
		import('phpexcel.PHPExcel', EXTEND_PATH); 
	    ob_end_clean();
	    if(empty($filename)) $filename = time();  
	    if( !is_array($indexKey)) return false;  

	    $header_arr = array('A','B','C','D','E','F','G','H','I','J','K','L','M', 'N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ','BA','BB','BC','BD','BE','BF','BG','BH','BI','BJ','BK','BL','BM','BN','BO','BP','BQ','BR','BS','BT','BU','BV','BW','BX','BY','BZ');
	    //初始化PHPExcel()  
	    $objPHPExcel = new \PHPExcel();  
	    $sheetname = $filename;
	    //设置保存版本格式  
	    if($excel2007){  
	        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);  
	        $filename = $filename.'.xlsx';  
	    }else{  
	        $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);  
	        $filename = $filename.'.xls';  
	    }  

	    //接下来就是写数据到表格里面去  
	    $objActSheet = $objPHPExcel->getActiveSheet()->setTitle($sheetname);  
	    //$startRow = 1;  
	    $idx = 0;
	    foreach ($indexKey as $key => $value) {
	    	//这里是设置单元格的内容  
	        $objActSheet->setCellValue($header_arr[$idx].$startRow,$key);
	        $objActSheet->getStyle($header_arr[$idx].$startRow)->getFont()->setBold(true);
	        $objActSheet->getColumnDimension($header_arr[$idx])->setWidth(15);
	        $idx++;
	    }
	    $startRow++;
	    foreach ($list as $row) {
	    	$idx = 0;
	        foreach ($indexKey as $key => $value){
	        	$v = "";
	        	if(array_key_exists($value, $row)) {
	        		$v = $row[$value];
	        	}
	            //这里是设置单元格的内容
	            if($value=='身份证号') {
    				$v = "'".$v;
	            } 
	            $objActSheet->setCellValue($header_arr[$idx].$startRow,$v);
	            $idx++;
	        }  
	        $startRow++;  
	    }  

	    // 下载这个表格，在浏览器输出  
	    header("Pragma: public");  
	    header("Expires: 0");  
	    header("Cache-Control:must-revalidate, post-check=0, pre-check=0");  
	    header("Content-Type:application/force-download");  
	    header("Content-Type:application/vnd.ms-execl");  
	    header("Content-Type:application/octet-stream");  
	    header("Content-Type:application/download");;  
	    header('Content-Disposition:attachment;filename='.$filename.'');  
	    header("Content-Transfer-Encoding:binary");  
	    $objWriter->save('php://output');
	}

	public static function importExcel($file) {
		//移到/public/uploads/excel/下
	    $info = $file->move(ROOT_PATH.'public'.DS.'uploads'.DS.'excel');
	    //上传文件成功
	    if ($info) {
	      	//获取上传后的文件名
	      	$fileName = $info->getSaveName();
		    //文件路径
		    $filePath = ROOT_PATH.'public/uploads/excel/'.$fileName;
	      	import('phpexcel.PHPExcel', EXTEND_PATH);
	      	$extension = strtolower( pathinfo($fileName, PATHINFO_EXTENSION) );
			if ($extension =='xlsx') {
			    $PHPReader = new \PHPExcel_Reader_Excel2007();
			} else if ($extension =='xls') {
			    $PHPReader = new \PHPExcel_Reader_Excel5();
			} else if ($extension=='csv') {
			    $PHPReader = new \PHPExcel_Reader_CSV();
			}
		    //读取excel文件
		    $objPHPExcel = $PHPReader->load($filePath);
		    //读取excel文件中的第一个工作表
		    $sheet = $objPHPExcel->getSheet(0);
    		$highestColumn = $sheet->getHighestColumn(); // 取得总列数 
    		++$highestColumn;
		    $highestRow = $sheet->getHighestRow();  //取得总行数
		    $data=array();
		    $param=[];
			for($j=1;$j<=$highestRow;$j++){
		        //从A列读取数据
		        $i = 0;
		        for($k='A';$k!=$highestColumn;$k++){
		        	$v = $objPHPExcel->getActiveSheet()->getCell("$k$j")->getValue();
		        	if($k == 'A' && !$v) break;
		        	if($j == 1) {
						$v = preg_replace("/\n|\s|\r/","",$v);
						if(strlen($v) == 0) {
							continue;
						}
						$param[$i] = $v;
					}else {
						if(array_key_exists($i, $param)) {
							$data[$j][$param[$i]]=$objPHPExcel->getActiveSheet()->getCell("$k$j")->getFormattedValue();
						}
					}
		            $i = $i + 1;
		        }
		    }
		    return $data;
		}else{
		    return [];
		}
	}

}